﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Data;

namespace Dal
{
    public class DalProfessional
    {
        SqlCommand cmd = null;
        string sql = "";

        public DataTable Nav_Inquire_All()
        {
            //一级导航全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select * from Navigation";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//一级导航全部信息

        public DataTable SubNav_Inquire_All(ModelPublic m)
        {
            //二级导航全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select * from SubNavigation where BelongTo='" + m.NavId + "'";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//二级导航全部信息

        public string Professional_Id(ModelPublic m)
        {
            //信息编号
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "select Id from Major where Title='" + m.Title + "' and Deleted='false' and NavId='"+m.NavId+"' and NavSubId='" + m.NavSubId + "'";

                cmd = new SqlCommand(sql, conn);
                string id = cmd.ExecuteScalar().ToString();
                if (id != "")
                    return id;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "";
        }//信息编号

        public DataTable Professional_Search_All(ModelPublic m)
        {
            //检索信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                if (m.UserType == "0")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Major where (Title like '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Recommend='True' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Major where (Title like '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Recommend='False' and Remarks='stu' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(发布版)

        public DataTable Professional_Search_Unpublished(ModelPublic m)
        {
            //检索信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                if (m.UserType == "0")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Major where (Title like '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Recommend='False' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Major where (Title like '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Recommend='False' and Remarks='stu' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(未发布版)

        public bool Professional_Delete(ModelPublic m)
        {
            //信息删除
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                string sql = "update Major set Deleted='true' where Id='" + m.Id + "'";

                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息删除

        public DataTable Professional_Inquire_All(ModelPublic m)
        {
            //获取全部信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                if (m.UserType == "0")
                    sql = "select Id,NavSubId,Title,CONVERT(VARCHAR(10),DateTime,120) as DateTime from Major where NavId='" + m.NavId + "' and Deleted='false' and Recommend='True' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,NavSubId,Title,CONVERT(VARCHAR(10),DateTime,120) as DateTime from Major where NavId='" + m.NavId + "' and Deleted='false' and Recommend='False' and Remarks='stu' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(发布版)

        public DataTable Professional_Inquire_All_Unpublished(ModelPublic m)
        {
            //获取全部信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                if (m.UserType == "0")
                    sql = "select Id,NavSubId,Title,CONVERT(VARCHAR(10),DateTime,120) as DateTime from Major where NavId='" + m.NavId + "' and  Deleted='false' and Recommend='False' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,NavSubId,Title,CONVERT(VARCHAR(10),DateTime,120) as DateTime from Major where NavId='" + m.NavId + "' and  Deleted='false' and Recommend='False' and Remarks='stu' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(未发布版)

        public DataTable Professional_Inquire_Single(ModelPublic m)
        {
            //查询单条信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select Id,NavSubId,Title,_Content,Author,CONVERT(VARCHAR(10),DateTime,120) as DateTime,Number,Recommend from Major where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt.Rows.Count > 0)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//查询单条信息

        public bool Professional_Modify(ModelPublic m)
        {
            //专业信息修改
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "update Major set Title='" + m.Title + "',_Content='" + m.Content + "',Author='" + m.Author + "',DateTime='" + m.DateTime + "',NavSubId='" + m.NavSubId + "',Number='" + m.Number + "',Recommend='"+m.Recommend+"' where Id='" + m.Id + "'";

                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//专业信息修改

        public bool Professional_Is(ModelPublic m)
        {
            //判断专业是否存在
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "select count(*) from Major where NavSubId='"+m.NavSubId+"' and Deleted='False'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteScalar().ToString() != "0")
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//判断专业是否存在

        public bool Professional_Add(ModelPublic m)
        {
            //信息添加
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "insert into Major values('" + m.NavId + "','" + m.NavSubId + "','" + m.Title + "','" + m.Content + "','" + m.Author + "','" + m.DateTime + "','" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "','" + m.Number + "','" + m.Recommend + "'," + m.Remarks + ",'false')";

                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息添加

    }
}
